package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Customer;
import ModelLayer.Discount;
import ModelLayer.Enum;

public class DBCustomer implements IFDBCustomer {
	private Connection con;

	public DBCustomer() {
		con = DBConnection.getInstance().getDBcon();

	}

	@Override
	public Customer getCustomer(int customerId, boolean retrieveAssociation) {
		String wClause = "  customerId = '" + customerId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertCustomer(Customer customer) throws Exception {
		int nextId = GetMaxId.getMaxId("Select max(customerId) From Customer") + 1;
		System.out.println("next customerId = " + nextId);
		int rc = -1;
		String query = "INSERT INTO customer(customerId, name, address, postCode, city, phone, customerType, discount_Id) VALUES('"
				+ nextId
				+ "','"
				+ customer.getName()
				+ "','"
				+ customer.getAddress()
				+ "','"
				+ customer.getPostCode()
				+ "','"
				+ customer.getCity()
				+ "','"
				+ customer.getPhone()
				+ "','"
				+ customer.getCustomerType().toString()
				+ "','"
				+ customer.getDiscount().getDiscountId() + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
		} catch (SQLException ex) {
			System.out.println("Customer is not inserted");
			throw new Exception("Customer is not inserted correct");
		}
		return rc;
	}

	@Override
	public ArrayList<Customer> getAllCustomers(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public int updateCustomer(Customer customer) {
		Customer customerObj = customer;
		int rc = -1;
		String query = "UPDATE customer SET " + "name = '"
				+ customerObj.getName() + "'," + "address = '"
				+ customerObj.getAddress() + "'," + "postCode = '"
				+ customerObj.getPostCode() + "'," + "city = '"
				+ customerObj.getCity() + "'," + "phone = '"
				+ customerObj.getPhone() + "'," + "customerType = '"
				+ customerObj.getCustomerType().toString() + "',"
				+ "discount_Id = '" + customerObj.getDiscount().getDiscountId()
				+ "'" + " WHERE customerId = '" + customerObj.getId() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			// TODO maybe make it throw exception
			System.out.println("Update exception in customer db: " + e);
		}
		return rc;

	}

	@Override
	public int deleteCustomer(int customerId) {
		int rc = -1;

		String query = "DELETE FROM customer WHERE customerId = '" + customerId
				+ "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in customer db: " + ex);
		}
		return (rc);
	}

	private Customer singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;

		Customer customerObj = new Customer();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				customerObj = buildCustomer(results);
				stmt.close();
				if (retrieveAssociation) {
					IFDBDiscount dbDiscountObj = new DBDiscount();
					Discount discountObj = dbDiscountObj
							.getDiscount(customerObj.getDiscount()
									.getDiscountId());
					customerObj.setDiscount(discountObj);
					System.out.println("Discount is selected");
				} else {
					customerObj = null;
				}
			}
		} catch (Exception e) {
			// TODO need to throw exception class
			System.out.println("Query exception: " + e);
		}
		return customerObj;
	}

	private ArrayList<Customer> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Customer> list = new ArrayList<Customer>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Customer customerObj = new Customer();
				customerObj = buildCustomer(results);
				list.add(customerObj);
			}
			stmt.close();
			if (retrieveAssociation) {
				for (Customer customerObj : list) {
					IFDBDiscount dbDiscountObj = new DBDiscount();
					Discount discountObj = dbDiscountObj
							.getDiscount(customerObj.getDiscount()
									.getDiscountId());
					customerObj.setDiscount(discountObj);
					System.out.println("Discount is selected");
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Customer buildCustomer(ResultSet results) {
		Customer customerObj = new Customer();
		try {
			customerObj.setId(results.getInt("customerId"));
			customerObj.setName(results.getString("name"));
			customerObj.setAddress(results.getString("address"));
			customerObj.setCity(results.getString("city"));
			customerObj.setPhone(results.getInt("phone"));
			customerObj.setPostCode(results.getInt("postCode"));
			customerObj.setCustomerType(Enum.valueOf(results
					.getString("customerType")));
			customerObj
					.setDiscount(new Discount(results.getInt("discount_Id")));
		} catch (Exception e) {
			// TODO need to throw exception class
			System.out.println("error in building the customer object");
		}
		return customerObj;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT customerId, name, address, postCode, city, phone, customerType, discount_Id FROM customer";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}

}
